<?php
/**
 * This file contains the class MemberDB which provides
 * all of the database requirements for the Member page
 *
 * @copyright  2009 A Memory Tree
 * @license    A Memory Tree License
 * @version    $Id:$
 * @link       http://my-trac.assembla.com/amemorytree/ticket/??
 * @since      File available since Release 0.1
 */
/**
 * This class provides access to the database for MemberDB
 *
 * @copyright  2009 A Memory Tree
 * @license    A Memory Tree License
 * @version    Release: @package_version@
 * @link       http://my-trac.assembla.com/amemorytree/ticket/??
 * @since      Class available since Release 0.1
 */
class MemberDB
{
    /**
      * Provides access to the database
     *
     * @var Db
     */
    private $_db;
    /**
     * Create a new instance of this class
     * 
     * @return void
     * @param Db $db
     */
    function __construct($db)
    {
        $this->_db = $db;
    }
    /**
     * Escape the provided value
     *
     * @return string
     * @param mixed $value
     */
    function escape($value)
    {
        $db = $this->_db;
        return $db->escape($value);
    }
    /**
     * Get the id number of the user
     *
     * @return int
     * @param mixed $username
     */
    function getUserIdFromUsername($username)
    {
        $db = $this->_db;
        $userId = 0;
        $sql = <<< EOT
            SELECT ID
            FROM Users
            WHERE Username = '%s'
EOT;
        $sql = sprintf($sql, $db->escape($username));
        $result = $db->query($sql);
        if ($db->rowCount($result) > 0) {
            $row = $db->fetch($result);
            $userId = intval($row['ID']);
        }
        return $userId;
    }
    /**
     * Get the id number of the user
     *
     * @return int
     * @param mixed $address
     */
    function getUserIdFromEmail($address)
    {
        $db = $this->_db;
        $userId = 0;
        $sql = <<< EOT
            SELECT ID
            FROM Users
            WHERE Email = '%s'
EOT;
        $sql = sprintf($sql, $db->escape($address));
        $result = $db->query($sql);
        if ($db->rowCount($result) > 0) {
            $row = $db->fetch($result);
            $userId = intval($row['ID']);
        }
        return $userId;
    }
    /**
     * Get the id number of the user
     *
     * @return int
     * @param mixed $name
     */
    function getUserId($name)
    {
        $db = $this->_db;
        $userId = $this->getUserIdFromUsername($name);
        if (empty($userId)) {
            $userId = $this->getUserIdFromEmail($name);
        }
        return $userId;
    }
    /**
     * Get the id number of the user
     *
     * @return string
     * @param int $userId
     */
    function getUsername($userId)
    {
        $db = $this->_db;
        $username = '';
        $sql = <<< EOT
            SELECT Username
            FROM Users
            WHERE ID = %d
EOT;
        $sql = sprintf($sql, $userId);
        $result = $db->query($sql);
        if ($db->rowCount($result) === 1) {
            $row = $db->fetch($result);
            $username = $db->unescape($row['Username']);
        }
        return $username;
    }
    /**
     * Get the users email address
     *
     * @return string
     * @param int $userId
     */
    function getEmail($userId)
    {
        $db = $this->_db;
        $email = '';
        $sql = <<< EOT
            SELECT Email
            FROM Users
            WHERE ID = %d
EOT;
        $sql = sprintf($sql, $userId);
        $result = $db->query($sql);
        if ($db->rowCount($result) > 0) {
            $row = $db->fetch($result);
            $value = $row['Email'];
            $email = $db->unescape($value);
        }
        return $email;
    }
    /**
     * Get the permissions that apply for this particular user
     *
     * @return string
     * @param int $userId
     */
    function getFuneralHomePermissions($userId)
    {
        $db = $this->_db;
        $permissions = '';
        $sql = <<< EOT
            SELECT Permissions
            FROM Users
            WHERE ID = %d
EOT;
        $sql = sprintf($sql, $userId);
        $result = $db->query($sql);
        if ($db->rowCount($result) > 0) {
            $row = $db->fetch($result);
            $permissions = $db->unescape($row['Permissions']);
        }
        return $permissions;
    }
    /**
     * Set the password
     *
     * @return bool
     * @param int $userId
     * @param mixed $passCode
     */
    function setPassword($userId, $passCode)
    {
        $db = $this->_db;
        $sql = <<< EOT
            UPDATE Users
            SET Password = '%s'
            WHERE ID = %d
EOT;
        $sql = sprintf($sql,
            $db->escape($passCode),
            $userId
        );
        $result = $db->query($sql);
        return $result === TRUE;
    }
    /**
     * Retrieve the pass code for a given user
     *
     * @return string
     * @param int $userId
     */
    function getPassword($userId)
    {
        $db = $this->_db;
        $sql = <<< EOT
            SELECT Password
            FROM Users
            WHERE ID = %d
EOT;
        $sql = sprintf($sql, $userId);
        $result = $db->query($sql);
        if ($db->rowCount($result) > 0) {
            $row = $db->fetch($result);
            $passCode = $db->unescape($row['Password']);
        }
        return $passCode;
    }
    /**
     * Get the authorisation code for the user
     *
     * @return string
     * @param int $userId
     */
    function getAuthCode($userId)
    {
        $db = $this->_db;
        $authCode = '';
        $sql = <<< EOT
            SELECT Code
            FROM Authentication
                INNER JOIN Users
                    ON Authentication.Id = Users.AuthId
            WHERE Users.ID = %d
EOT;
        $sql = sprintf($sql, $userId);
        $result = $db->query($sql);
        if ($db->rowCount($result) > 0) {
            $row = $db->fetch($result);
            $authCode = $db->unescape($row['Code']);
        }
        return $authCode;

    }
    /**
     * Remove a users reference to an authorisation code
     *
     * @return bool
     * @param int $userId
     */
    function removeAuthCode($userId)
    {
        $db = $this->_db;
        $sql = <<< EOT
            UPDATE Users
            SET AuthId = NULL
            WHERE ID = %d
EOT;
        $sql = sprintf($sql, $userId);
        $result = $db->query($sql);
        return $result === TRUE;
    }
    /**
     * Provide the name of the funeralhome for the user
     *
     * @return string
     * @param int $userId
     */
    function getFuneralHomeName($userId)
    {
        $db = $this->_db;
        $funeralHomeName = '';
        $sql = <<< EOT
            SELECT
                Name
            FROM
                FuneralHome
                    INNER JOIN Users
                        ON FuneralHome.ID = Users.FuneralHomeID
            WHERE
                Users.ID = %d
EOT;
        $sql = sprintf($sql, $userId);
        $result = $db->query($sql);
        if ($db->rowCount($result) > 0) {
            $row = $db->fetch($result);
            $funeralHomeName = $db->unescape($row['Name']);
        }
        return $funeralHomeName;
    }
}
?>